package com.kpl.kplstzble.utils;

import android.util.Log;
import com.kpl.kplstzble.entity.CfData;
import com.kpl.kplstzble.entity.HistoryData;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;


/**
 * 数据库工具类：连接数据库用、获取数据库数据用
 * 相关操作数据库的方法均可写在该类
 */
public class DBUtils {

    private static String driver = "com.mysql.jdbc.Driver";// MySql驱动

//  private static String url = "jdbc:mysql://localhost:3306/map_designer_test_db";

    private static String user = "root";// 用户名

    private static String password = "135328";// 密码

    private static Connection getConn() {

        Connection connection = null;
        try {
            Class.forName(driver);// 动态加载类
            // 尝试建立到给定数据库URL的连接
            connection = DriverManager.getConnection("jdbc:mysql://39.107.104.228:3306/kplstzble?ueSSL=false&serverTimezone=Asia/Shanghai",
                    user, password);

        } catch (Exception e) {
            e.printStackTrace();
        }

        return connection;
    }

    public  static  boolean savedata(String devicename,String datainfo,String munum,String sumdistance,String datestring,int datediffer,int worktype){
        Connection connection = getConn();
        // 插入数据的 sql 语句
        String sql = "insert into measure_data (devicename,datainfo,munum,distance,date,datediffer,worktype) values (?,?,?,?,?,?,?)";
        PreparedStatement ps = null;
        if (connection == null) {
            return false;
        }
        try {
            ps = connection.prepareStatement(sql);
            // 为两个 ? 设置具体的值
            ps.setString(1, devicename);
            ps.setString(2, datainfo);
            ps.setString(3, munum);
            ps.setString(4, sumdistance);
            ps.setString(5, datestring);
            ps.setInt(6, datediffer);
            ps.setInt(7,worktype);
            // 执行语句
            ps.executeUpdate();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }


    //获取测亩数据


    public static CfData getCfData(String devicecode){
        // HashMap<String, Object> map = new HashMap<>();
        CfData cfData = new CfData();

        // 根据数据库名称，建立连接
        Connection connection = getConn();

        try {
            // mysql简单的查询语句。这里是根据MD_CHARGER表的NAME字段来查询某条记录
            String sql = "select * from sys_data where deviceid = (select id from sys_device where devicecode = ?)  order by gettime desc limit 0,1";
//            String sql = "select * from MD_CHARGER";
            if (connection != null){// connection不为null表示与数据库建立了连接
                PreparedStatement ps = connection.prepareStatement(sql);
                if (ps != null){
                    // 设置上面的sql语句中的？的值为name
                    // 执行sql查询语句并返回结果集
                    ps.setString(1, devicecode);
                    ResultSet rs = ps.executeQuery();
                    if (rs != null){
                        while (rs.next()){
                            cfData.setId(rs.getInt("id"));
                            cfData.setDeviceid(rs.getInt("deviceid"));
                            cfData.setLongitude(rs.getDouble("longitude"));
                            cfData.setLatitude(rs.getDouble("latitude"));
                            cfData.setSpeed(rs.getDouble("speed"));
                            Timestamp gettime = rs.getTimestamp("gettime");
                            cfData.setGettime(gettime);
                            // }
                        }
                        connection.close();
                        ps.close();
                        return  cfData;
                    }else {
                        return null;
                    }
                }else {
                    return  null;
                }
            }else {
                return  null;
            }
        }catch (Exception e){
            e.printStackTrace();
            Log.e("DBUtils","异常：" + e.getMessage());
            return null;
        }

    }



    //获取显示器最新编号
    public static Integer getdisplaycode() {
        // 根据数据库名称，建立连接
        Connection connection = getConn();
        try {
            // mysql简单的查询语句。这里是根据MD_CHARGER表的NAME字段来查询某条记录
            String sql = "select * from sys_displaycode order by id desc limit 0, 1";
            if (connection != null) {// connection不为null表示与数据库建立了连接
                PreparedStatement ps = connection.prepareStatement(sql);
                if (ps != null) {
                    // 设置上面的sql语句中的？的值为name
                    // 执行sql查询语句并返回结果集
                    ResultSet rs = ps.executeQuery();
                    if (rs != null) {
                        Integer displaycode = null;
                        while (rs.next()) {
                            displaycode = rs.getInt("displaycode");
                        }
                        connection.close();
                        ps.close();
                        return displaycode;
                    } else {
                        return null;
                    }
                } else {
                    return null;
                }
            } else {
                return null;
            }
        } catch (Exception e) {
            e.printStackTrace();
            Log.e("DBUtils", "异常：" + e.getMessage());
            return null;
        }
    }

    public  static  boolean savedisplaycode(Integer displaycode){
        Connection connection = getConn();
        // 插入数据的 sql 语句
       // String sql = "insert into sys_displaycode (displaycode) values (?)";
        String sql = "insert into sys_displaycode (displaycode) select "+displaycode+"  from dual where not exists (select * from sys_displaycode where displaycode="+displaycode+" limit 1)";

        PreparedStatement ps = null;
        if (connection == null) {
            return false;
        }
        try {
            ps = connection.prepareStatement(sql);
            // 为两个 ? 设置具体的值
            //ps.setInt(1, displaycode);
            // 执行语句
            ps.executeUpdate();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static ArrayList<HistoryData> gethistorydata(){
        // HashMap<String, Object> map = new HashMap<>();
        ArrayList<HistoryData> list=new ArrayList<HistoryData>();
        // 根据数据库名称，建立连接
        Connection connection = getConn();

        try {
            // mysql简单的查询语句。这里是根据MD_CHARGER表的NAME字段来查询某条记录
            String sql = "select * from measure_data order by date";
//            String sql = "select * from MD_CHARGER";
            if (connection != null){// connection不为null表示与数据库建立了连接
                PreparedStatement ps = connection.prepareStatement(sql);
                if (ps != null){
                    // 设置上面的sql语句中的？的值为name
                    // 执行sql查询语句并返回结果集
                    ResultSet rs = ps.executeQuery();
                    if (rs != null){
                        int count = rs.getMetaData().getColumnCount();
                        Log.e("DBUtils","列总数：" + count);
                        while (rs.next()){
                            HistoryData historyData = new HistoryData();
                            historyData.setId(rs.getInt("id"));
                            historyData.setDatainfo(rs.getString("datainfo"));
                            historyData.setMunum(rs.getString("munum"));
                            historyData.setDistance(rs.getString("distance"));
                            historyData.setDate(rs.getString("date"));
                            historyData.setDatediffer(rs.getString("datediffer"));
                            historyData.setWorktype(rs.getInt("worktype"));
                            // 注意：下标是从1开始的
                            //for (int i = 1;i <= count;i++){
                            // String field = rs.getMetaData().getColumnName(i);
                            //map.put("edition", rs.getString("edition"));
                            list.add(historyData);
                            // }
                        }
                        connection.close();
                        ps.close();
                        return  list;
                    }else {
                        return null;
                    }
                }else {
                    return  null;
                }
            }else {
                return  null;
            }
        }catch (Exception e){
            e.printStackTrace();
            Log.e("DBUtils","异常：" + e.getMessage());
            return null;
        }

    }


    public  static  boolean deleteonedata(int id){
        Connection connection = getConn();
        // 插入数据的 sql 语句
        String sql = "delete from  measure_data where id = ?";
        PreparedStatement ps = null;
        if (connection == null) {
            return false;
        }
        try {
            ps = connection.prepareStatement(sql);
            // 为两个 ? 设置具体的值
            ps.setInt(1, id);
            // 执行语句
            ps.executeUpdate();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }



    public static String getbleversion(){

        // HashMap<String, Object> map = new HashMap<>();
        // 根据数据库名称，建立连接
        Connection connection = getConn();

        try {
            // mysql简单的查询语句。这里是根据MD_CHARGER表的NAME字段来查询某条记录
            String sql = "select * from bleversion";
            if (connection != null){// connection不为null表示与数据库建立了连接
                PreparedStatement ps = connection.prepareStatement(sql);
                if (ps != null){
                    // 设置上面的sql语句中的？的值为name
                    // 执行sql查询语句并返回结果集
                    ResultSet rs = ps.executeQuery();
                    if (rs != null){
                        String version =null;
                        while (rs.next()){
                            version= rs.getString("version");
                        }
                        connection.close();
                        ps.close();
                        return  version;
                    }else {
                        return null;
                    }
                }else {
                    return  null;
                }
            }else {
                return  null;
            }
        }catch (Exception e){
            e.printStackTrace();
            Log.e("DBUtils","异常：" + e.getMessage());
            return null;
        }

    }

}
